use tempdb 
go


IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOT NULL,
mgrid INT NULL,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
)
GO

IF OBJECT_ID('Departments') IS NOT NULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT
)
GO

-- fill datas
INSERT INTO employees VALUES  (1,NULL,'Nancy',00.00)
INSERT INTO employees VALUES  (2,1,'Andrew',00.00)
INSERT INTO employees VALUES  (3,1,'Janet',00.00)
INSERT INTO employees VALUES  (4,1,'Margaret',00.00)
INSERT INTO employees VALUES  (5,2,'Steven',00.00)
INSERT INTO employees VALUES  (6,2,'Michael',00.00)
INSERT INTO employees VALUES  (7,3,'Robert',00.00)
INSERT INTO employees VALUES  (8,3,'Laura',00.00)
INSERT INTO employees VALUES  (9,3,'Ann',00.00)
INSERT INTO employees VALUES  (10,4,'Ina',00.00)
INSERT INTO employees VALUES  (11,7,'David',00.00)
INSERT INTO employees VALUES  (12,7,'Ron',00.00)
INSERT INTO employees VALUES  (13,7,'Dan',00.00)
INSERT INTO employees VALUES  (14,11,'James',00.00)

INSERT INTO departments VALUES  (1,'HR',2)
INSERT INTO departments VALUES  (2,'Marketing',7)
INSERT INTO departments VALUES  (3,'Finance',8)
INSERT INTO departments VALUES  (4,'R&D',9)
INSERT INTO departments VALUES  (5,'Training',4)
INSERT INTO departments VALUES  (6,'Gardening',NULL)
GO

select * from Employees 
SELECT * FROM departments

-- table-value function
IF OBJECT_ID('fn_getsubtree') IS NOT NULL
DROP FUNCTION  fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
RETURNS TABLE 
AS 
RETURN(
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS 
  (
    -- Anchor Member (AM)
    SELECT empid, empname, mgrid, 0
    FROM employees
    WHERE empid = @empid   
    UNION ALL
    -- Recursive Member (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM employees AS e
       join employees_subtree AS es
          ON e.mgrid = es.empid
  )
    SELECT * FROM Employees_Subtree
)
GO

select * from fn_getsubtree(1)


-- cross apply query
SELECT *
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST



-- outer apply query
SELECT *
FROM Departments AS D
    OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST